package com.starinside.wp.repository.impl;

import com.starinside.wp.foundation.vo.BindedBabyVO;
import com.starinside.wp.repository.BabyNativeRepository;
import com.starinside.wp.repository.BaseNativeSqlRepository;
import com.starinside.wp.utils.DateUtil;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@Repository
public class BabyNativeRepositoryImpl extends BaseNativeSqlRepository implements BabyNativeRepository {

//    public List<BindedBabyVO> getAllBindedBaby(String mobile){
////        String sql = "select id,aid as 'areaId',name,gender,birthday,nickName,relation,imgUrl from children c inner join (" +
////                "select b.relation,b.cid from userchildren b where b.uid = (" +
////                String.format("select a.id from users a where a.phone ='%s')) d on d.cid=c.id", mobile);
//
//        String sql = "select c.id, c.aid as 'areaId', c.name, c.gender, c.birthday,  c.status,c.nickName, d.relation, c.imgUrl, group_concat(cl.id) as classIds, group_concat(cl.name) as classNames,c.tuoRemainingNum,c.zaoRemainingNum,c.lastZaoNum,c.lastTuoNum,cl.category " +
//                "from children c  " +
//                "inner join (select b.relation,b.cid from userchildren b where b.uid = (select a.id from users a where a.phone = ?1)) d on d.cid=c.id " +
//                "left join rel_classes_children rcc on c.id = rcc.childrenId " +
//                "left join classes cl on rcc.classesId = cl.id " +
//                "group by  c.id, c.aid " +
//                "order by birthday desc";
//
//        List<BindedBabyVO> items = sqlObjectList(sql, Arrays.asList(mobile), BindedBabyVO.class);
//
//        for (BindedBabyVO baby : items){
//            baby.setTuoRemainingNum(baby.getTuoRemainingNum() + baby.getLastTuoNum());
//            baby.setZaoRemainingNum(baby.getZaoRemainingNum() + baby.getLastZaoNum());
//            baby.setMonth(monthIndex(baby.getBirthday()));
//        }
//
//        return items;
//    }

    public List<BindedBabyVO> getAllBindedBaby(String mobile) {
        String sql = "select c.id, c.aid as 'areaId', c.name, c.gender, c.birthday, c.status, c.nickName, d.relation, c.imgUrl, " +
                "group_concat(cl.id) as classIds, group_concat(cl.name) as classNames, " +
                "c.tuoRemainingNum, c.zaoRemainingNum, c.lastZaoNum, c.lastTuoNum, cl.category " +
                "from children c " +
                "inner join (select b.relation, b.cid from userchildren b where b.uid = " +
                "(select a.id from users a where a.phone = ?1)) d on d.cid = c.id " +
                "left join rel_classes_children rcc on c.id = rcc.childrenId AND rcc.status = 1 " +
                "left join classes cl on rcc.classesId = cl.id " +
                "group by c.id, c.aid, c.name, c.gender, c.birthday, c.status, c.nickName, " +
                "d.relation, c.imgUrl, c.tuoRemainingNum, c.zaoRemainingNum, c.lastZaoNum, " +
                "c.lastTuoNum, cl.category " +
                "order by c.birthday desc";

        List<BindedBabyVO> items = sqlObjectList(sql, Arrays.asList(mobile), BindedBabyVO.class);

        for (BindedBabyVO baby : items) {
            baby.setTuoRemainingNum(baby.getTuoRemainingNum() + baby.getLastTuoNum());
            baby.setZaoRemainingNum(baby.getZaoRemainingNum() + baby.getLastZaoNum());
            baby.setMonth(monthIndex(baby.getBirthday()));
        }

        return items;
    }

    public List<BindedBabyVO> getAllBindedBaby(String[] ids){
        String sql = "select c.id, c.aid as 'areaId', c.name, c.status,c.gender, c.birthday, c.nickName, c.imgUrl from children c where c.id in (?1)";

        List params = new ArrayList();
        params.add(Arrays.asList(ids));

        List<BindedBabyVO> items = sqlObjectList(sql, params, BindedBabyVO.class);

        for (BindedBabyVO baby : items){
            baby.setMonth(monthIndex(baby.getBirthday()));
        }

        return items;
    }

    public List<BindedBabyVO> getAllBindedBabyByParent(String parentId){
//        String sql = "select id,aid as 'areaId',name,gender,birthday,nickName,relation,imgUrl from children c inner join (" +
//                "select b.relation,b.cid from userchildren b where b.uid = '" + parentId + "') d on d.cid = c.id";
        String sql = "select c.id, c.aid as 'areaId', c.name, c.status,c.gender, c.birthday, c.nickName, d.relation, c.imgUrl, " +
                " c.crmClienteleId," +
                " group_concat(cl.id) as classIds, group_concat(cl.name) as classNames " +
                "from children c  " +
                String.format("inner join (select b.relation,b.cid from userchildren b where b.uid = '%s') d on d.cid=c.id ", parentId) +
                "left join rel_classes_children rcc on c.id = rcc.childrenId " +
                "left join classes cl on rcc.classesId = cl.id " +
                "group by c.id, c.aid " +
                "order by birthday desc";

        List<BindedBabyVO> items = sqlObjectList(sql, BindedBabyVO.class);

        return items;
    }

    public BindedBabyVO getPreBindedBaby(String mobile, String babyId){
        String sql = "select id,aid as 'areaId',name,status,gender,birthday,nickName,relation,imgUrl from children c inner join (" +
                "select b.relation,b.cid from userchildren b where b.uid = (" +
                String.format("select a.id from users a where a.phone ='%s')) d on d.cid=c.id and c.id='%s'", mobile, babyId);

        List<BindedBabyVO> items = sqlObjectList(sql, BindedBabyVO.class);

        if(items.size() == 0){
            return null;
        }

        BindedBabyVO ret = items.get(0);

        ret.setMonth(monthIndex(ret.getBirthday()));

        return ret;
    }

    private int monthIndex(Date date){
        Date today = new Date();
        String birthday = date.toString();
        Integer monthInterval = null;//月龄
        try {
            monthInterval = DateUtil.betweenMonths(DateUtil.millisToDateTime(today.getTime(), "yyyy-MM-dd"), birthday);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return monthInterval;
    }

    public BindedBabyVO getBindedBaby(String id){
        List<BindedBabyVO> items = sqlObjectList("select id,aid as 'areaId',name,status,gender,birthday,nickName from children c where c.id='" + id + "'", BindedBabyVO.class);

        if(items.size() > 0){
            BindedBabyVO baby = items.get(0);
            baby.setMonth(monthIndex(baby.getBirthday()));
            return baby;
        }

        return null;
    }

    public BindedBabyVO getBindedBabyById(String cid, String pid){
        String sql = "select id,aid as 'areaId',name,status,gender,birthday,nickName,relation,imgUrl from children c inner join (" +
                String.format("select b.relation,b.cid from userchildren b where b.cid='%s' and b.uid = '%s') d on d.cid=c.id", cid, pid);

        List<BindedBabyVO> items = sqlObjectList(sql, BindedBabyVO.class);

        if(items.size() > 0){
            BindedBabyVO baby = items.get(0);

            baby.setMonth(monthIndex(baby.getBirthday()));

            return baby;
        }

        return null;
    }

//    public BindedBabyVO getBindedBaby(String id, String mobile){
//
//        String sql = "select c.id, c.aid as 'areaId', c.name, c.gender, c.birthday,  c.status,c.nickName, d.relation, c.imgUrl, " +
//                " c.crmClienteleId, group_concat(cl.id) as classIds, group_concat(cl.name) as classNames " +
//                "from children c  " +
//                "inner join (select b.relation,b.cid from userchildren b where b.uid = (select a.id from users a where a.phone = ?1)) d on d.cid=c.id " +
//                "left join rel_classes_children rcc on c.id = rcc.childrenId " +
//                "left join classes cl on rcc.classesId = cl.id " +
//                "where c.id = ?2";
//
//        List<BindedBabyVO> items = sqlObjectList(sql, Arrays.asList(mobile, id), BindedBabyVO.class);
//
//        for (BindedBabyVO baby : items){
//            baby.setMonth(monthIndex(baby.getBirthday()));
//        }
//
//        return items.size() > 0 ? items.get(0) : null;

//        String sql = "select id,crmClienteleId,aid as 'areaId',name,status,gender,birthday,nickName,relation,imgUrl from children c inner join (" +
//                String.format("select b.relation,b.cid from userchildren b where b.cid='%s' and b.uid = (", id) +
//                String.format("select a.id from users a where a.phone ='%s')) d on d.cid=c.id",  mobile);
//
//        List<BindedBabyVO> items = sqlObjectList(sql, BindedBabyVO.class);
//
//        if(items.size() > 0){
//            BindedBabyVO baby = items.get(0);
//
//            baby.setMonth(monthIndex(baby.getBirthday()));
//
//            return baby;
//        }
//
//        return null;
//    }

    public BindedBabyVO getBindedBaby(String id, String mobile) {
        String sql = "select c.id, c.aid as 'areaId', c.name, c.gender, c.birthday, c.status, c.nickName, d.relation, c.imgUrl, " +
                "c.crmClienteleId, group_concat(cl.id) as classIds, group_concat(cl.name) as classNames " +
                "from children c " +
                "inner join (select b.relation,b.cid from userchildren b where b.uid = (select a.id from users a where a.phone = ?1)) d on d.cid=c.id " +
                "left join rel_classes_children rcc on c.id = rcc.childrenId " +
                "left join classes cl on rcc.classesId = cl.id " +
                "where c.id = ?2 " +
                "group by c.id, c.aid, c.name, c.gender, c.birthday, c.status, c.nickName, d.relation, c.imgUrl, c.crmClienteleId";

        List<BindedBabyVO> items = sqlObjectList(sql, Arrays.asList(mobile, id), BindedBabyVO.class);

        for (BindedBabyVO baby : items) {
            baby.setMonth(monthIndex(baby.getBirthday()));
        }

        return items.size() > 0 ? items.get(0) : null;
    }


    @Override
    public List<BindedBabyVO> getAllBindedBabyEffective(String[] ids) {

        String sql = "SELECT  childrenId as id ,c.`status` ,c.aid as areaId  FROM children_extend_info cei INNER JOIN children c  on c.id = cei.childrenId  where  " +
                "cei.childrenId in (?1)   and (cei.cameraValidTo > NOW()  OR cei.cameraValidTo is  null ) ";

        List params = new ArrayList();
        params.add(Arrays.asList(ids));

        List<BindedBabyVO> items = sqlObjectList(sql, params, BindedBabyVO.class);

        return items;
    }
}
